﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace JYH.Framework
{

    public class NPOIMemoryStream : MemoryStream
    {
        /// <summary>
        /// 获取流是否关闭
        /// </summary>
        public bool IsColse
        {
            get;
            private set;
        }
        public NPOIMemoryStream(bool colse = false)
        {
            IsColse = colse;
        }
        public override void Close()
        {
            if (IsColse)
            {
                base.Close();
            }
        }
    }
    public class NPOIExcelHelper
    {

        public IWorkbook RWorkbook
        {
            get;set;
        }
        public FileStream RFileStream
        {
            get;set;
        }
        public List<ISheet> Sheets
        {
            get;set;
        }

        public List<IRow> IRows
        {
            get;set;
        }

        /// <summary>
        /// 获取读取 WorkBook
        /// </summary>
        public bool GetReadWorkbook(string excelPath,out string Msg)
        {
          
            Msg=string.Empty;

            try
            {
                string _extensionName = System.IO.Path.GetExtension(excelPath);
                //  RFileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                NPOIMemoryStream stream = new NPOIMemoryStream();
                using (var fileStream = File.Open(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    fileStream.CopyTo(stream);
                    stream.Position = 0; //这一句不加就会上面的异常错误
                    if (_extensionName.Equals(".xls"))
                    {
                        RWorkbook = new HSSFWorkbook(stream);
                    }
                    // 把xlsx 写入workbook中 2
                    else if (_extensionName.Equals(".xlsx"))
                    {
                        RWorkbook = new XSSFWorkbook(stream);
                    }
                    else
                    {
                        RWorkbook = null;
                        Msg = "此文件非excel文件，无法读取数据";
                        return false;
                    }
                }
       
                return true;
            }
            catch (Exception ex)
            {
                Msg = ex.Message;
                return false;

            }
            finally
            {
                RFileStream.Close();
            }
           
        }
        /// <summary>
        /// 获取表中的Sheet名称
        /// </summary>
        /// <summary>
        /// 获取所有 Sheet表
        /// </summary>
        public void GetSheets(string excelPath, out string Msg)
        {
             Msg=string.Empty;
            bool res = GetReadWorkbook(excelPath, out Msg);
            if (!res)
            {
                Sheets = null;
                return;
            }
            else
            {
                Sheets = new List<ISheet>();
                var _sheetCount = RWorkbook.NumberOfSheets;
                for (int i = 0; i < _sheetCount; i++)
                {
                    Sheets.Add(RWorkbook.GetSheetAt(i));
                }
            }

            // 获取表
          
        }
        /// <summary>
        /// 删除其中一个Sheet
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="excelPath"></param>
        /// <returns></returns>
        public bool RemoveOneSheet( string sheetName, string excelPath,out string Msg)
        {
            Msg=string.Empty;
            // 创建文件流
    
            try
            {

                Msg = string.Empty;
                bool res = GetReadWorkbook(excelPath, out Msg);
                if (!res)
                {
                    
                    Sheets = null;
                    return false;
                }
                else
                {
                    // 1. 通过Sheet名字查找Sheet下标
                    var _sheetIndex = RWorkbook.GetSheetIndex(sheetName);
                    if (_sheetIndex >= 0)
                    {
                        // 2. 通过Sheet下标移除 Sheet
                        RWorkbook.RemoveSheetAt(_sheetIndex);
                        // 3. 对 Workbook 的修改写入文件流，对文件进行相应操作
                        RWorkbook.Write(RFileStream);
                        return true;
                    }
                    else
                    {
                        Msg = sheetName + "Sheet不存在";
                        return false;
                    }
                }
            }
            catch (Exception ex)
            {
                Msg = ex.ToString();
                return false;
            }
          




    }
        /// <summary>
        /// 清空 Sheet指定行数据
        /// </summary>
        /// <param name="rowNum"></param>
        /// <param name="sheetName"></param>
        /// <param name="excelPath"></param>
        /// <returns></returns>
        public bool EmptySheetRow(int rowNum, string sheetName, string excelPath,out string Msg)
        {
            Msg = string.Empty;
            try
            {

                bool res = GetReadWorkbook(excelPath, out Msg);
                if (!res)
                {

                    Sheets = null;
                    return false;
                }
                //1.通过Sheet名 获取对应的 ISheet
                ISheet _sheet = RWorkbook.GetSheet(sheetName);
                if (_sheet != null)
                {
                    // 2. 定位到要删除的指定行
                    IRow _row = _sheet.GetRow(rowNum - 1);
                    if (_row != null)
                    {
                        // 3. 清空行数据
                        _sheet.RemoveRow(_row);
                        // 4. 对 Workbook 的修改写入文件流，对文件进行相应操作；
                        RWorkbook.Write(RFileStream);
                        return true;
                    }
                    else
                    {
                        Msg = "不存在此行数据";
                        return false;
                    }
                }
                else
                {
                   Msg="This the Sheet does not exist";
                    return false;
                }
            }
            catch (Exception ex)
            {
                Msg= ex.ToString();
                return false;
            }
            finally
            {
                RFileStream.Close();
            }
        }

        /// <summary>
        /// 写入Excel
        /// </summary>
        /// <param name="Row"></param>
        /// <param name="SheetIndex"></param>
        /// <param name="excelPath"></param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public bool WriteSheetRow(IRow Row, int SheetIndex, string excelPath, out string Msg)
        {
            Msg= string.Empty;
            try
            {
                bool res = GetReadWorkbook(excelPath, out Msg);
                if (!res)
                {

                    Sheets = null;
                    return false;
                }
                //1.通过Sheet名 获取对应的 ISheet
                ISheet _sheet = RWorkbook.GetSheetAt(SheetIndex);
                if (_sheet != null)
                {
                    
                    IRow r = _sheet.CreateRow(_sheet.LastRowNum + 1);
                    for (int i = 0; i < r.LastCellNum; i++)
                    {
                        if (i < Row.Cells.Count)
                        {
                            r.Cells[i].SetCellValue(Row.Cells[i].StringCellValue);
                        }
                    }
                    RWorkbook.Write(RFileStream);
                }
                else
                {
                    Msg = "Sheet不存在";
                    return false;
                }
                return true;
             }
            catch (Exception ex)
            {
                Msg = ex.ToString();
                return false;
            }
            finally
            {
                RFileStream.Close();
            }
        }










    }

}
